import sqlexecx
from config import PGSQL
from threading import Thread

create_table_sql = '''
CREATE TABLE IF NOT EXISTS person (
  id BIGSERIAL primary key,
  name varchar(45) NOT NULL,
  age int NOT NULL,
  birth_date date DEFAULT NULL,
  sex smallint DEFAULT NULL,
  grade float DEFAULT NULL,
  point decimal(8,2) DEFAULT NULL,
  money decimal(8,4) DEFAULT NULL,
  create_by bigint DEFAULT NULL,
  create_time timestamp DEFAULT CURRENT_TIMESTAMP,
  update_by bigint DEFAULT NULL,
  update_time timestamp DEFAULT NULL,
  del_flag smallint NOT NULL DEFAULT 0
) 
'''

def full_test():
    select_key = "SELECT currval('person_id_seq')"
    print(sqlexecx.save('person', name='aaa', age=54))
    print(sqlexecx.save_sql('INSERT INTO person(name,age) VALUES(?,?)', 'lisi', 26))
    print(sqlexecx.insert(table_name='person', name='wangwu', age=38))
    print(sqlexecx.save_sql_select_key(select_key, 'INSERT INTO person(name,age) VALUES(:name, :age)', name='zhaoliu', age=45))

    print(sqlexecx.select('select * from person'))
    print(sqlexecx.select_one('select * from person'))

    print(sqlexecx.query('select id, name, age from person'))
    print(sqlexecx.query_one('select id, name, age from person where name = ?', 'lisi'))
    print(sqlexecx.query_one('select id, name, age from person where name = :name', name='lisi'))

    print(sqlexecx.get('select count(1) from person'))

    # print(sqlexecx.select_page('select id, name, age from person', 1, 2))
    # print(sqlexecx.query_page('select id, name, age from person', 1, 2))

    sqlexecx.execute('delete from person where id = ?', 4)


if __name__ == '__main__':
    # from psycopg2 import connect
    sqlexecx.init(**PGSQL, debug=True, driver='psycopg2')
    # sqlexecx.init_db(f"postgres://{PGSQL['user']}:{PGSQL['password']}@{PGSQL['host']}:{PGSQL['port']}/{PGSQL['database']}", show_sql=True, debug=True)
    sqlexecx.execute(create_table_sql)
    # sqlexecx.execute('truncate table person')
    # full_test()

    for _ in range(1):
        t = Thread(target=full_test()).start()